﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using vnFingerPrint.BusinessObject;
using vnCommon;
namespace vnFingerPrint.DataAccess
{
    public static class CongDan_DataAccess
    {
        //Select CongDan
        public static List<CongDan_Object> SelectAll()
        {
            string SelectQuery = @"Select * from [CongDan]";
            List<CongDan_Object> result = new List<CongDan_Object>();
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery);
            foreach (DataRow row in tb.Rows)
            {
                CongDan_Object newObject = new CongDan_Object();
                if (row["HoTen"].ToString() != "") newObject.HoTen = (string)row["HoTen"];
                if (row["NgaySinh"].ToString() != "") newObject.NgaySinh = (DateTime)row["NgaySinh"];
                if (row["GioiTinh"].ToString() != "") newObject.GioiTinh = (string)row["GioiTinh"];
                newObject.SoCMND = (string)row["SoCMND"];
                if (row["SoHoChieu"].ToString() != "") newObject.SoHoChieu = (string)row["SoHoChieu"];
                if (row["NgayCap"].ToString() != "") newObject.NgayCap = (DateTime)row["NgayCap"];
                if (row["NoiCap"].ToString() != "") newObject.NoiCap = (string)row["NoiCap"];
                if (row["Id_TinhThanh"].ToString() != "") newObject.Id_TinhThanh = (int)row["Id_TinhThanh"];
                if (row["Id_QuanHuyen"].ToString() != "") newObject.Id_QuanHuyen = (int)row["Id_QuanHuyen"];
                if (row["PhuongXa"].ToString() != "") newObject.PhuongXa = (string)row["PhuongXa"];
                if (row["DiaChi"].ToString() != "") newObject.DiaChi = (string)row["DiaChi"];
                if (row["GhiChu"].ToString() != "") newObject.GhiChu = (string)row["GhiChu"];
                if (row["ImageUrl"].ToString() != "") newObject.ImageUrl = (string)row["ImageUrl"];
                result.Add(newObject);
            }
            return result;
        }
        //Insert CongDan
        public static int Insert(CongDan_Object newObject)
        {
            string InsertQuery = "Insert Into [CongDan]";
            List<SqlParameter> paralist = new List<SqlParameter>();
            string _val = "", _para = "";
            if (newObject.HoTen != null)
            {
                _val += "HoTen, "; _para += "@HoTen, ";
                paralist.Add(new SqlParameter("HoTen", newObject.HoTen));
            }
            if (newObject.NgaySinh != null)
            {
                _val += "NgaySinh, "; _para += "@NgaySinh, ";
                paralist.Add(new SqlParameter("NgaySinh", newObject.NgaySinh));
            }
            if (newObject.GioiTinh != null)
            {
                _val += "GioiTinh, "; _para += "@GioiTinh, ";
                paralist.Add(new SqlParameter("GioiTinh", newObject.GioiTinh));
            }
            _val += "SoCMND, "; _para += "@SoCMND, ";
            paralist.Add(new SqlParameter("SoCMND", newObject.SoCMND));
            if (newObject.SoHoChieu != null)
            {
                _val += "SoHoChieu, "; _para += "@SoHoChieu, ";
                paralist.Add(new SqlParameter("SoHoChieu", newObject.SoHoChieu));
            }
            if (newObject.NgayCap != null)
            {
                _val += "NgayCap, "; _para += "@NgayCap, ";
                paralist.Add(new SqlParameter("NgayCap", newObject.NgayCap));
            }
            if (newObject.NoiCap != null)
            {
                _val += "NoiCap, "; _para += "@NoiCap, ";
                paralist.Add(new SqlParameter("NoiCap", newObject.NoiCap));
            }
            if (newObject.Id_TinhThanh != null)
            {
                _val += "Id_TinhThanh, "; _para += "@Id_TinhThanh, ";
                paralist.Add(new SqlParameter("Id_TinhThanh", newObject.Id_TinhThanh));
            }
            if (newObject.Id_QuanHuyen != null)
            {
                _val += "Id_QuanHuyen, "; _para += "@Id_QuanHuyen, ";
                paralist.Add(new SqlParameter("Id_QuanHuyen", newObject.Id_QuanHuyen));
            }
            if (newObject.PhuongXa != null)
            {
                _val += "PhuongXa, "; _para += "@PhuongXa, ";
                paralist.Add(new SqlParameter("PhuongXa", newObject.PhuongXa));
            }
            if (newObject.DiaChi != null)
            {
                _val += "DiaChi, "; _para += "@DiaChi, ";
                paralist.Add(new SqlParameter("DiaChi", newObject.DiaChi));
            }
            if (newObject.GhiChu != null)
            {
                _val += "GhiChu, "; _para += "@GhiChu, ";
                paralist.Add(new SqlParameter("GhiChu", newObject.GhiChu));
            }
            if (newObject.ImageUrl != null)
            {
                _val += "ImageUrl, "; _para += "@ImageUrl, ";
                paralist.Add(new SqlParameter("ImageUrl", newObject.ImageUrl));
            }
            
            InsertQuery += "(" + _val + ") Values (" + _para + ")";
            InsertQuery = InsertQuery.Replace(", )", ")");
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), InsertQuery, paralist);
        }
        //Get an Object of CongDan
        public static CongDan_Object GetObject(string _SoCMND)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            CongDan_Object newObject = new CongDan_Object();
            string SelectQuery = "Select * from [CongDan] where (1=1) and [SoCMND]= @SoCMND";
            paralist.Add(new SqlParameter("SoCMND", _SoCMND));
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery, paralist);
            if (tb.Rows.Count < 1) return null;
            DataRow row = tb.Rows[0];
            if (row["HoTen"].ToString() != "") newObject.HoTen = (string)row["HoTen"];
            if (row["NgaySinh"].ToString() != "") newObject.NgaySinh = (DateTime)row["NgaySinh"];
            if (row["GioiTinh"].ToString() != "") newObject.GioiTinh = (string)row["GioiTinh"];
            newObject.SoCMND = (string)row["SoCMND"];
            if (row["SoHoChieu"].ToString() != "") newObject.SoHoChieu = (string)row["SoHoChieu"];
            if (row["NgayCap"].ToString() != "") newObject.NgayCap = (DateTime)row["NgayCap"];
            if (row["NoiCap"].ToString() != "") newObject.NoiCap = (string)row["NoiCap"];
            if (row["Id_TinhThanh"].ToString() != "") newObject.Id_TinhThanh = (int)row["Id_TinhThanh"];
            if (row["Id_QuanHuyen"].ToString() != "") newObject.Id_QuanHuyen = (int)row["Id_QuanHuyen"];
            if (row["PhuongXa"].ToString() != "") newObject.PhuongXa = (string)row["PhuongXa"];
            if (row["DiaChi"].ToString() != "") newObject.DiaChi = (string)row["DiaChi"];
            if (row["GhiChu"].ToString() != "") newObject.GhiChu = (string)row["GhiChu"];
            if (row["ImageUrl"].ToString() != "") newObject.ImageUrl = (string)row["ImageUrl"];
            return newObject;
        }
        //Save changes of an Object to database CongDan
        public static int SaveChanged(CongDan_Object UpdatingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string UpdateQuery =
            @"Update CongDan SET
               HoTen= @HoTen,
               NgaySinh= @NgaySinh,
               GioiTinh= @GioiTinh,
               SoHoChieu= @SoHoChieu,
               NgayCap= @NgayCap,
               NoiCap= @NoiCap,
               Id_TinhThanh= @Id_TinhThanh,
               Id_QuanHuyen= @Id_QuanHuyen,
               PhuongXa= @PhuongXa,
               DiaChi= @DiaChi,
               GhiChu= @GhiChu,
               ImageUrl= @ImageUrl
            Where SoCMND= @SoCMND";
            paralist.Add(new SqlParameter("HoTen", UpdatingObject.HoTen));
            paralist.Add(new SqlParameter("NgaySinh", UpdatingObject.NgaySinh));
            paralist.Add(new SqlParameter("GioiTinh", UpdatingObject.GioiTinh));
            paralist.Add(new SqlParameter("SoCMND", UpdatingObject.SoCMND));
            paralist.Add(new SqlParameter("SoHoChieu", UpdatingObject.SoHoChieu));
            paralist.Add(new SqlParameter("NgayCap", UpdatingObject.NgayCap));
            paralist.Add(new SqlParameter("NoiCap", UpdatingObject.NoiCap));
            paralist.Add(new SqlParameter("Id_TinhThanh", UpdatingObject.Id_TinhThanh));
            paralist.Add(new SqlParameter("Id_QuanHuyen", UpdatingObject.Id_QuanHuyen));
            paralist.Add(new SqlParameter("PhuongXa", UpdatingObject.PhuongXa));
            paralist.Add(new SqlParameter("DiaChi", UpdatingObject.DiaChi));
            paralist.Add(new SqlParameter("GhiChu", UpdatingObject.GhiChu));
            paralist.Add(new SqlParameter("ImageUrl", UpdatingObject.ImageUrl));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), UpdateQuery, paralist);
        }
        //Delete an Object in database CongDan
        public static int Delete(CongDan_Object DeletingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string DeleteQuery =
            @"Delete [CongDan]
            Where SoCMND= @SoCMND";
            paralist.Add(new SqlParameter("SoCMND", DeletingObject.SoCMND));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), DeleteQuery, paralist);
        }
    }
}